﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AioNightly.Model;
using MySql.Data.MySqlClient;

namespace AioNightly.Dao
{
    public class PVersionDAO
    {
        private void Read2List(MySqlDataReader reader, List<PVersion> list)
        {
            while (reader.Read())
            {
                PVersion it = new PVersion();
                it.Id = reader.GetInt32(0);
                it.ProductId = reader.GetInt32(1);
                it.VersionName = reader.GetString(2);

                list.Add(it);
            }
        }

        public List<PVersion> GetVersionByProductName(string productName)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            List<PVersion> list = new List<PVersion>();
            string sql = string.Format("SELECT   v.Id, v.ProductId, v.VersionName, p.Id AS Expr1, p.ProductName FROM pversion v INNER JOIN product p ON v.ProductId = p.Id WHERE (p.ProductName = '{0}')", productName);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            MySqlDataReader reader = cmd.ExecuteReader();
            

            Read2List(reader, list);

            if (reader != null)
                reader.Close();
            db.CloseConnection();

            return list;
        }

        public int GetVersioinIdByVersionName(string name)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            List<PVersion> list = new List<PVersion>();
            string sql = string.Format("select * from pversion where versionname='{0}'", name);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            MySqlDataReader reader = cmd.ExecuteReader();

            Read2List(reader, list);

            if (reader != null)
                reader.Close();

            db.CloseConnection();

            if (list.Count == 1)
            {
                return list[0].Id;
            }
            else
            {
                return -1;
            }
        }

        public int AddPVersion(int productId, string versionName)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            string sql = string.Format("insert into pversion (productid,versionname) values ({0},'{1}')", productId, versionName);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            int result = cmd.ExecuteNonQuery();

            return result;
        }
    }
}